package at.jku.se.pr.quickshopperserver;


import java.sql.*;
import java.util.*;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;


/**
 * Simple Java Program to connect Oracle database by using Oracle JDBC thin driver.
 * Make sure you have Oracle JDBC thin driver in your WEB-INF folder before running this program.
 */
public class DBConnection {
	
	/**
	 * URL of Oracle database
	 */
	private final String url = "jdbc:oracle:thin:@//localhost:1521/XE";
	
	/**
	 * User
	 */
	private final String user = "hr";
	
	/**
	 * Password
	 */
	private final String pwd = "Group1group";
	
	/**
	 * The Connection to the Oracle database
	 */
	private Connection conn;
	
	/**
	 * Properties for creating connection to Oracle database
	 */
	private Properties props;
	
	/**
	 * @param autoCommitOff
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
    public void connect(boolean autoCommitOff) throws ClassNotFoundException, SQLException {
    	props = new Properties();
    	props.setProperty("user", user);
        props.setProperty("password", pwd);
        
        // creating connection to Oracle database using JDBC
        Class.forName("oracle.jdbc.OracleDriver");
        conn = DriverManager.getConnection(url, props);
        
        if (autoCommitOff) conn.setAutoCommit(false);
    }
    
    /**
     * method to check whether the customer exists in the database
     * @param obj
     * @return
     * @throws SQLException
     * @throws JSONException
     */
    public String checkCustomerExists(JSONObject obj) throws SQLException, JSONException {
    	String sql = "SELECT vorname, nachname FROM kunde WHERE kunde_id = " + obj.getLong("kunde_id");
    	System.out.println("sql: " + sql);
        
        // creating PreparedStatement object to execute query
        PreparedStatement preStatement = conn.prepareStatement(sql);
        
        ResultSet result = preStatement.executeQuery();
        while(result.next()) {
        	// test output
            System.out.println("kunde_id: " + obj.getLong("kunde_id") + ", name: " + result.getString("vorname") + " " + result.getString("nachname"));
            obj.put("name", result.getString("vorname") + " " + result.getString("nachname"));
            return obj.toString();
        }
        
        // error
        obj.put("name", "Kunde nicht vorhanden");
        return obj.toString();
    }
    
    /**
     * method to get 'bezeichnung' and 'einzelpreis' of the given product
     * @param obj
     * @return string of the given JSON-Object
     * @throws SQLException
     * @throws JSONException
     */
    public String getDataForEanNr(JSONObject obj) throws SQLException, JSONException {
        String sql = "SELECT bezeichnung, einzelpreis FROM produkt WHERE produkt_id = " + obj.getLong("produkt_id");
        System.out.println("sql: " + sql);
        
        // creating PreparedStatement object to execute query
        PreparedStatement preStatement = conn.prepareStatement(sql);
        
        ResultSet result = preStatement.executeQuery();
        while(result.next()) {
        	// test output
            System.out.println("produkt_id: " + obj.getLong("produkt_id") + ", bezeichnung: " + result.getString("bezeichnung") + ", einzelpreis " + result.getDouble("einzelpreis"));
            obj.put("bezeichnung", result.getString("bezeichnung"));
            obj.put("einzelpreis", result.getDouble("einzelpreis"));
            return obj.toString();
        }
        
        // error
        obj.put("bezeichnung", "Produkt nicht vorhanden");
        obj.put("einzelpreis", 0.0);
        return obj.toString();
    }
    
    /**
     * method to check whether the given barcode exists in the database 
     * if yes, return the string of the barcode 
     * @param obj
     * @return string of the given JSON-Object(barcode)
     * @throws SQLException
     * @throws JSONException
     */
    public String getDataForCashdesk(JSONObject obj) throws SQLException, JSONException {
    	 String sql = "SELECT kassabarcode FROM filiale WHERE kassabarcode = " + obj.getLong("kassabarcode");
         System.out.println("sql: " + sql);
         
         // creating PreparedStatement object to execute query
         PreparedStatement preStatement = conn.prepareStatement(sql);
         
         ResultSet result = preStatement.executeQuery();
         while(result.next()) {
             obj.put("kassa", "ok");
             return obj.toString();
         }
         
         // error
         obj.put("kassa", "nok");
         return obj.toString();
    }
    
    /**
     * method to add a new buying-entry(new row) to the database
     * @param arr
     * @return the string "ok"
     * @throws SQLException
     * @throws JSONException
     */
	public String saveShoppinglist(JSONArray arr) throws SQLException, JSONException {
		JSONObject o;
		
		// Kunde
		o = arr.getJSONObject(0);
		long customerEAN = o.getLong("kunde_id");
		
		// Filiale
		o = arr.getJSONObject(1);
		long cashdeskEAN = o.getLong("filiale");
		
		// Menge
		o = arr.getJSONObject(2);
		int sumAmount = o.getInt("menge");
		
		// Gesamtpreis
		o = arr.getJSONObject(3);
		double sumPrice = o.getDouble("gesamtpreis");
		
		// in Tabelle einkauf einfuegen
		String sql = "INSERT INTO einkauf (kaufdatum, kunde_id, menge, gesamtpreis, filiale) " +
				"VALUES (SYSDATE, " + customerEAN + ", " + sumAmount + ", " + sumPrice + ", " + cashdeskEAN + ")";
		System.out.println("sql: " + sql);
		
        PreparedStatement preStatement = conn.prepareStatement(sql);
        preStatement.executeUpdate();
        
        int lastShoppingID = getLastShoppingID();
        
		for (int i = 4; i < arr.length(); i++) {
			o = arr.getJSONObject(i);
			
			// Produkt
			long productEAN = o.getLong("ean");
			
			// Menge
			int amount = o.getInt("menge");
			
			// Einzelpreis
			double price = o.getDouble("preis");
			
			// in Tabelle produkteinkauf einfuegen
			sql = "INSERT INTO produkteinkauf (kauf_id, kaufdatum, kunde_id, produkt_id, menge, einzelpreis) " +
					"VALUES (" + lastShoppingID + ", SYSDATE, " + customerEAN + ", " + productEAN + ", " + amount + ", " + price + ")";
			System.out.println("sql: " + sql);
			System.out.println();
			
	        preStatement = conn.prepareStatement(sql);
	        preStatement.executeUpdate();
		}
		
		return "ok";
	}
	
	/**
	 * method to get the ID of the current buying 
	 * @return the last added shopping ID in the database as int
	 * @throws SQLException
	 */
	private int getLastShoppingID() throws SQLException {
		String sql = "SELECT kauf_id FROM einkauf WHERE kauf_id = (SELECT MAX(kauf_id) FROM einkauf)";
		
		// creating PreparedStatement object to execute query
        PreparedStatement preStatement = conn.prepareStatement(sql);
        
        ResultSet result = preStatement.executeQuery();
        while(result.next()) {
            return result.getInt("kauf_id");
        }
        
        // error
        return -1;
	}
	
	/**
	 * commits the Connection to the Oracle database
	 * @throws SQLException
	 */
	public void commit() throws SQLException {
		conn.commit();
	}
    
}
